﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_ThanhphandinhduongthucanDAL : ClsConnection
    {
        public DataTable Thanhphandinhduong_ByNhomId_Tenthucan(int NhomthucphamId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(QueryString.TBLTHUCPHAM_THUCANBYNHOMTHUCANID, cn);
            //comand.CommandType = CommandType.StoredProcedure;
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            try
            {
                comand.Parameters.Add("@NhomthucanId", OleDbType.Integer).Value = NhomthucphamId;
                comand.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }

        public DataTable Thanhphandinhduong_ByNhomId_Tenthucan(string Tenthucan, int NhomthucphamId)
        {
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);
                string query = "SELECT     TblThucpham_thucan.Tenthucan, TblDm_Donvitinh.Tendonvitinh, "
            + "   TblThucpham_thucan.Calo, TblThanhphanhoahoc.Tphh_Protid, TblThanhphanhoahoc.Tphh_Lipid, TblThanhphanhoahoc.Tphh_GlucId,  "
            + "   TblThanhphanhoahoc.Tphh_Nuoc, TblThanhphanhoahoc.Tphh_Cellulose, TblThanhphanhoahoc.Tphh_Tro,  "
            + "   TblThanhphanhoahoc.Tpmk_Natri, TblThanhphanhoahoc.Tpmk_Kali, TblThanhphanhoahoc.Tpmk_Calci,  "
            + "   TblThanhphanhoahoc.Tpmk_Photpho, TblThanhphanhoahoc.Tpmk_Fe, TblThanhphanhoahoc.Tpmk_Axitfomic,  "
            + "   TblThanhphanhoahoc.Tpvitamin_VitaminA, TblThanhphanhoahoc.Tpvitamin_VitaminC,  "
            + "   TblThanhphanhoahoc.Tpvitamin_PP, TblThanhphanhoahoc.Tpvitamin_Caroten,  "
            + "   TblDm_Nhomthucpham_thucan.Id AS Nhom,  "
            + "   TblThucpham_thucan.Nguyenlieu, TblThucpham_thucan.Nguondongvat,  "
            + "   TblThucpham_thucan.Tylethai,	TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Nam, TblThucpham_thucan.Id "
            + " FROM ((TblDm_Nhomthucpham_thucan INNER JOIN "
            + "       TblThucpham_thucan ON TblDm_Nhomthucpham_thucan.Id = TblThucpham_thucan.NhomthucanId) INNER JOIN "
            + "       TblThanhphanhoahoc ON TblThucpham_thucan.Id = TblThanhphanhoahoc.IdThucpham_thucan) INNER JOIN "
            + "       TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id "
            + " WHERE  TblThucpham_thucan.Tenthucan LIKE '%" + Tenthucan + "%' AND TblDm_Nhomthucpham_thucan.Id = " + NhomthucphamId.ToString();
                OleDbConnection cn = ClsConnection.OleDBConnect();
                OleDbCommand comand = new OleDbCommand(query, cn);

                try
                {
                    comand.ExecuteNonQuery();
                    OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                    Adapter.Fill(table);
                    cn.Close();
                }
                catch (System.Exception ex)
                { //
                    MessageBox.Show("Error " + ex.Message); 
                }
                return table;
        }

        public DataTable Thanhphandinhduong_ByNhomId_Tenthucan(string Tenthucan)
        {
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);
            string query = "SELECT     TblThucpham_thucan.Tenthucan, TblDm_Donvitinh.Tendonvitinh, "
        + "   TblThucpham_thucan.Calo, TblThanhphanhoahoc.Tphh_Protid, TblThanhphanhoahoc.Tphh_Lipid, TblThanhphanhoahoc.Tphh_GlucId,  "
        + "   TblThanhphanhoahoc.Tphh_Nuoc, TblThanhphanhoahoc.Tphh_Cellulose, TblThanhphanhoahoc.Tphh_Tro,  "
        + "   TblThanhphanhoahoc.Tpmk_Natri, TblThanhphanhoahoc.Tpmk_Kali, TblThanhphanhoahoc.Tpmk_Calci,  "
        + "   TblThanhphanhoahoc.Tpmk_Photpho, TblThanhphanhoahoc.Tpmk_Fe, TblThanhphanhoahoc.Tpmk_Axitfomic,  "
        + "   TblThanhphanhoahoc.Tpvitamin_VitaminA, TblThanhphanhoahoc.Tpvitamin_VitaminC,  "
        + "   TblThanhphanhoahoc.Tpvitamin_PP, TblThanhphanhoahoc.Tpvitamin_Caroten,  "
        + "   TblDm_Nhomthucpham_thucan.Id AS Nhom,  "
        + "   TblThucpham_thucan.Nguyenlieu, TblThucpham_thucan.Nguondongvat,  "
        + "   TblThucpham_thucan.Tylethai,	TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Nam, TblThucpham_thucan.Id "
        + " FROM ((TblDm_Nhomthucpham_thucan INNER JOIN "
        + "       TblThucpham_thucan ON TblDm_Nhomthucpham_thucan.Id = TblThucpham_thucan.NhomthucanId) INNER JOIN "
        + "       TblThanhphanhoahoc ON TblThucpham_thucan.Id = TblThanhphanhoahoc.IdThucpham_thucan) INNER JOIN "
        + "       TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id "
        + " WHERE  TblThucpham_thucan.Tenthucan LIKE '%" + Tenthucan + "%' ";
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(query, cn);

            try
            {
                comand.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Error " + ex.Message); 
            }
            return table;
        }

        public DataTable GetTable(int Name)
        {
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            string namePro = QueryString.PRCTBLDM_THANHPHANDINHDUONG_SELECTBYNAME;
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(namePro, cn);
            //comand.CommandType = CommandType.StoredProcedure;

            try
            {
                comand.Parameters.Add("@Id_Nhom", OleDbType.Integer).Value = Name;
                comand.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { 
                MessageBox.Show("Error " + ex.Message); 
            }
            return table;
        
        }

        public DataTable GetTable()
        {
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            string namePro = QueryString.PRCTBLDM_THANHPHANDINHDUONG_SELECTALL;
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(namePro, cn);
            //comand.CommandType = CommandType.StoredProcedure;

            try
            {
                comand.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }

        public void Execute_Pro(string proName, string Value, int Values)
        {
            SqlConnection cn = SQLConnect();
            SqlCommand comand = new SqlCommand(proName, cn);
            comand.CommandType = CommandType.StoredProcedure;
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            try
            {
                comand.Parameters.Add(Value, SqlDbType.Int).Value = Values;
                comand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show("Error " + ex.Message);
            }
        }

        public void Delete(string Value, int Values)
        {
            DataTable table = new DataTable();
            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            try
            {
                string nameSP = QueryString.PRCTBLDM_THANHPHANHOCHOC_DELETE;
                OleDbConnection cn = OleDBConnect();
                OleDbCommand comand = new OleDbCommand(nameSP, cn);
                comand.Parameters.Add(Value, SqlDbType.Int).Value = Values;
                comand.ExecuteNonQuery();
                cn.Close();
                nameSP = QueryString.PRCTBLDM_THANHPHANDINHDUONGTHUCAN_DELETE;
                cn = OleDBConnect();
                comand = new OleDbCommand(nameSP, cn);
                comand.Parameters.Add(Value, SqlDbType.Int).Value = Values;
                comand.ExecuteNonQuery();
                cn.Close();
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show("Error " + ex.Message);
            }
        }
    }
}
